package edu.uwm.capstone.diasmart.database;

import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * Used to acces the category data object
 * 
 * @author Palaniappan
 * @version 1.0
 * 
 */
public class CategoryDAO {

	private static long DEFAULT_CATEGORY_ID = -1L;
	private static final String ORDER_BY_FIELDS = "user_order,category";
	private static final String SELECT_FIELDS;
	private DatabaseHelper dbh;

	static {
		StringBuilder selectFields = new StringBuilder();
		selectFields.append("_id,category,is_default");
		selectFields
				.append(",start_time,end_time,user_order,last_category_id,user_default");
		SELECT_FIELDS = selectFields.toString();
	}

	/**
	 * @param databaseHelper
	 */
	public CategoryDAO(DatabaseHelper databaseHelper) {
		this.dbh = databaseHelper;
	}

	/**
	 * @param db
	 * @param category
	 */
	/**
	 * @param db
	 * @param category
	 */
	public void insert(SQLiteDatabase db, Category category) {
		ContentValues newContent = new ContentValues();
		setContentValues(category, newContent);
		long l = db.insert("categories", null, newContent);
		category.setId(l);
		category.setModified(false);
		DatabaseHelper.fireTableRowChanged("categories", l);

	}

	/**
	 * @param category
	 * @param newContent
	 */
	private void setContentValues(Category category, ContentValues newContent) {
		newContent.put("category", category.getName());
		if (category.isDefault()) {
			newContent.put("is_default", Integer.valueOf(1));
		} else if (!category.isDefault()) {
			newContent.put("is_default", Integer.valueOf(0));
		}
		if (category.isUserDefault()) {
			newContent.put("user_default", Integer.valueOf(1));
		} else if (!category.isUserDefault()) {
			newContent.put("user_default", Integer.valueOf(0));
		}
		newContent.put("start_time", Long.valueOf(category.getStartTime()));
		newContent.put("end_time", Long.valueOf(category.getEndTime()));
		newContent.put("user_order", Integer.valueOf(category.getUserOrder()));
		newContent.put("last_category_id",
				Long.valueOf(category.getLastCategoryId()));

	}

	/**
	 * @param db
	 * @param category
	 */
	private void update(SQLiteDatabase db, Category category) {
		ContentValues newContent = new ContentValues();
		setContentValues(category, newContent);
		db.update("categories", newContent, "_id=" + category.getId(), null);
		category.setModified(false);
		DatabaseHelper.fireTableRowChanged("categories", category.getId());
	}

	/**
	 * @param paramLong
	 */
	public void delete(long paramLong) {
		if (paramLong == getDefaultCategoryId()) {
			this.dbh.getEntryDAO().updateCategory(paramLong,
					getDefaultCategoryId());
			this.dbh.getWritableDatabase().delete("categories",
					"_id=" + paramLong, null);
			DatabaseHelper.fireTableRowChanged("categories", paramLong);
		}
	}

	/**
	 * @param paramCategory
	 */
	public void delete(Category paramCategory) {
		delete(paramCategory.getId());
	}

	/**
	 * @return
	 */
	public long getDefaultCategoryId() {
		if (DEFAULT_CATEGORY_ID < 0L)
			DEFAULT_CATEGORY_ID = getDefaultCategory().getId();
		return DEFAULT_CATEGORY_ID;
	}

	/**
	 * @return
	 */
	public Category getDefaultCategory() {
		Cursor result = this.dbh.getReadableDatabase().rawQuery(
				"SELECT " + SELECT_FIELDS + " from " + "categories" + " where "
						+ "is_default" + " = 1", null);
		try {
			if (result.move(1)) {
				Category category = createCategory(result);
				return category;
			}
		} finally {
			if (result != null)
				result.close();
		}
		if (result != null)
			result.close();
		Category category = SystemData.getSystemUnfiledCategory(this.dbh
				.getContext());
		category.setModified(true);
		insert(this.dbh.getWritableDatabase(), category);
		return category;
	}

	/**
	 * @param result
	 * @return
	 */
	private Category createCategory(Cursor result) {

		long id = result.getLong(0);
		String name = result.getString(1);
		boolean isDefault = false;
		int i = result.getInt(2);
		long startTime = result.getLong(3);
		long endTime = result.getLong(4);
		int userOrder = result.getInt(5);
		long lastCategoryId = result.getLong(6);
		boolean isUserDefault = false;
		int j = result.getInt(7);

		if (i == 1) {
			isDefault = true;
		}
		if (j == 1) {
			isUserDefault = true;
		}

		return new Category(id, name, isDefault, startTime, endTime, userOrder,
				lastCategoryId, isUserDefault);
	}

	/**
	 * @return
	 */
	public Category[] getCategories() {
		ArrayList<Category> category = new ArrayList<Category>();
		Cursor result = this.dbh.getReadableDatabase().rawQuery(
				"SELECT " + SELECT_FIELDS + " from " + "categories"
						+ " order by " + ORDER_BY_FIELDS, null);
		try {
			while (result.move(1)) {
				Log.d("Diasmart", "While loop in getCategories()");
				category.add(createCategory(result));
			}
		} catch (Exception e) {
			Log.e("Diasmart",
					"Unexpected error occured in getCategories()"
							+ e.getLocalizedMessage());
		} finally {
			if (result != null)
				result.close();
		}
		return (Category[]) category.toArray(new Category[category.size()]);
	}

	/**
	 * @return
	 */
	public String[] getCategoriesAsString() {
		Category[] categories = getCategories();
		String[] category = new String[categories.length];
		for (int i = 0; i < categories.length; i++) {
			category[i] = categories[i].getName();
		}
		return category;
	}

	/**
	 * @param typeId
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	public Category[] getCategoriesUsed(int typeId, long startTime, long endTime) {
		ArrayList<Category> categories = new ArrayList<Category>();
		Cursor result = this.dbh.getReadableDatabase().rawQuery(
				"SELECT DISTINCT " + SELECT_FIELDS + " from " + "categories"
						+ " where " + "_id" + " in (" + "select DISTINCT "
						+ "category_id" + " from " + "entries" + " where "
						+ "type_id" + "=" + typeId + " AND " + "entry_time"
						+ ">=" + startTime + " and " + "entry_time" + "<="
						+ endTime + ")" + " order by " + ORDER_BY_FIELDS, null);
		try {
			while (result.move(1)) {
				categories.add(createCategory(result));
			}
		} catch (Exception e) {
			Log.d("Diasmart", "Unexpected error occured in getCategoriesUsed"
					+ e.getLocalizedMessage());
		} finally {
			if (result != null)
				result.close();
		}
		return (Category[]) categories.toArray(new Category[categories.size()]);
	}

	/**
	 * @param id
	 * @return
	 */
	/**
	 * @param id
	 * @return
	 */
	public Category getCategory(long id) {
		Cursor result = this.dbh.getReadableDatabase().rawQuery(
				"SELECT " + SELECT_FIELDS + " FROM " + "categories" + " WHERE "
						+ "_id" + " = " + id, null);
		try {
			if (result.move(1)) {
				Category category = createCategory(result);
				return category;
			}
		} catch (Exception e) {
			Log.e("Diasmart",
					"Unexpected error occured in getCategory"
							+ e.getLocalizedMessage());
		} finally {
			if (result != null)
				result.close();
		}
		if (result != null)
			result.close();
		return null;

	}

	/**
	 * @param category
	 * @return
	 */
	public int getCategoryId(String category) {
		Cursor result = this.dbh.getReadableDatabase().rawQuery(
				"SELECT _id from categories where category = ?",
				new String[] { category });
		try {
			if (result.move(1)) {
				int i = result.getInt(0);
				return i;
			}
		} catch (Exception e) {
			Log.e("Diasmart",
					"Unexpected error occured" + e.getLocalizedMessage());
		} finally {
			if (result != null)
				result.close();
		}
		if (result != null)
			result.close();
		return -1;
	}

	/**
	 * @param paramDate
	 * @param paramBoolean
	 * @return
	 */
	public Category getInitialCategory(Date paramDate, boolean paramBoolean) {
		return getInitialCategory(getCategories(), paramDate, paramBoolean);
	}

	/**
	 * @param categories
	 * @param date
	 * @param paramBoolean
	 * @return
	 */
	public Category getInitialCategory(Category[] categories, Date date,
			boolean paramBoolean) {
		DateFormat dateFormat = android.text.format.DateFormat
				.getTimeFormat(this.dbh.getContext());
		String str = dateFormat.format(date);
		int i;
		Category category = null;
		try {
			long startDate = dateFormat.parse(str).getTime();

			for (i = 0; i < categories.length; i++) {
				long startTime = categories[i].getStartTime();
				long endTime = categories[i].getEndTime();
				if (categories[i].isUserDefault())
					category = categories[i];
				if ((startTime != -1L) || (endTime != -1L)) {
					if (endTime < 0L)
						endTime = 9223372036854775807L;
					if (endTime < startTime) {
						if ((startDate >= startTime) || (startDate <= endTime))
							return categories[i];
					} else if ((startDate >= startTime)
							&& (startDate <= endTime))
						return categories[i];
				}
			}

		} catch (ParseException pe) {
			Log.e("DiaSmart",
					"Parse Exception occured" + pe.getLocalizedMessage());
		}
		return category;
	}

	/**
	 * @param category
	 */
	public void save(Category category) {
		if (category.isModified()) {
			if (category.getId() > 0L) {
				insert(this.dbh.getWritableDatabase(), category);
			}
			while (!category.isUserDefault()) {
				this.dbh.getWritableDatabase().execSQL(
						"UPDATE categories SET user_default=0 where _id<>"
								+ category.getId());
			}
			update(this.dbh.getWritableDatabase(), category);
		}

	}
}
